﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class turnier : Form
    {
        #region Variables

        SqlConnection con = new SqlConnection();
        public SqlDataAdapter adapter = null;

        public DataTable datatbl = new DataTable();
        private SqlCommandBuilder sqlCommandBuilder = null;
        private BindingSource bindingSource = null;
        public string selectquery = null;

        private int iRowIndex = 0;

        #endregion

        public turnier()
        {
            InitializeComponent();
        }

        private void turnier_Load(object sender, EventArgs e)
        {

        }

        private void buttonDBConnectt_Click(object sender, EventArgs e)
        {

            // Wenn keine Verbindung geoeffnet ist wird sie hergestellt.
            if (con.State == ConnectionState.Closed)
            {
                //Connection String
                con.ConnectionString = ("Data Source=localhost\\SQLEXPRESS; Initial Catalog = golf; Integrated Security = false; User ID = sa; Password = 12345; Connect Timeout = 3");

                con.Open();
            }

            // Wenn die Verbindung geoeffnet oder geschlossen ist, Gib Verbindungsstatus aus. 
            if (con.State == ConnectionState.Open)
            {
                MessageBox.Show("Datenbankverbindung wurde hergestellt");
            }
            else
            {
                MessageBox.Show("Datenbankverbindung wurde geschlossen");
            }
        }

        private void buttonSelectTurnier_Click(object sender, EventArgs e)
        {

            if (kt.Checked)
            {
                // Select Turnier
                Convert.ToByte(kt.Checked);
                string selectquery = string.Format("SELECT tName, tDatum, mName, mVorname, ts.Lochnr, ts.Schlagnr FROM turnierteilnehmer, turnier, mitglied, turnierstatistik");

                adapter = new SqlDataAdapter(selectquery, con);
                SqlCommandBuilder sqlbuilder = new SqlCommandBuilder(adapter);

                datatbl.Clear();
                //DataSet ds = new DataSet();

                adapter.Fill(datatbl);
                //adapter.Fill(ds, "mitglied");

                bindingSource = new BindingSource();
                bindingSource.DataSource = datatbl;

                // Render der Daten am schirm

                // Resize the DataGridView columns to fit the newly loaded content.
                //DG_ResultsGrid.AutoResizeColumns(
                //DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

                DgTurnier.DataSource = datatbl;
                //DG_ResultsGrid.DataSource = bindingSource;
                //DG_ResultsGrid.DataSource = ds.DefaultViewManager;
            }
            else if (pt.Checked)
            {
                // Select Turnier
                Convert.ToByte(pt.Checked);
                string selectquery = string.Format("SELECT tName, tDatum, gName, gVorname, ts.Lochnr, ts.Schlagnr FROM turnierteilnehmer, turnier, gast, turnierstatistik");

                adapter = new SqlDataAdapter(selectquery, con);
                SqlCommandBuilder sqlbuilder = new SqlCommandBuilder(adapter);

                datatbl.Clear();
                //DataSet ds = new DataSet();

                adapter.Fill(datatbl);
                //adapter.Fill(ds, "mitglied");

                bindingSource = new BindingSource();
                bindingSource.DataSource = datatbl;

                // Render der Daten am schirm

                // Resize the DataGridView columns to fit the newly loaded content.
                //DG_ResultsGrid.AutoResizeColumns(
                //DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

                DgTurnier.DataSource = datatbl;
                //DG_ResultsGrid.DataSource = bindingSource;
                //DG_ResultsGrid.DataSource = ds.DefaultViewManager;
            }
        }

        private void kt_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void buttonInsertTurnier_Click(object sender, EventArgs e)
        {
            // Querystring
            string query = "INSERT INTO turnier( tName, tDatum, tPublic) VALUES "
               + " ('" + txtName.Text + "','" + calDate.Text + "','" + pt.Checked + "')";

            SqlCommand cmd = new SqlCommand(query, con);
            //cmd.ExecuteNonQuery();
                                 

            int intCheckQuery = cmd.ExecuteNonQuery();

            // Prueft ob Query erfolgreich abgesetzt werden konnte.
            if (intCheckQuery > 0)
            {
                MessageBox.Show("Datensatz erfolgreich hinzugefügt");
            }
            else
            {
                MessageBox.Show("Datensatz konnte nicht hinzugefügt werden");
            }

            // Verbindung zur Datenbank schließen
            con.Close();

            

        }

        private void buttonUpdateTurnier_Click(object sender, EventArgs e)
        {
                       try
            {

                //adapter.Update(datatbl);
                adapter.Update(datatbl);


            }
            catch (Exception exceptionObj)
            {
                MessageBox.Show(exceptionObj.Message.ToString());
            }
        }

        private void buttonSelectTurnier_Click_1(object sender, EventArgs e)
        {
            if (kt.Checked)
            {
                // Select Turnier
                Convert.ToByte(kt.Checked);
                string selectquery = string.Format("SELECT tName, tDatum FROM turnier");

                adapter = new SqlDataAdapter(selectquery, con);
                SqlCommandBuilder sqlbuilder = new SqlCommandBuilder(adapter);

                datatbl.Clear();
                //DataSet ds = new DataSet();

                adapter.Fill(datatbl);
                //adapter.Fill(ds, "mitglied");

                bindingSource = new BindingSource();
                bindingSource.DataSource = datatbl;

                // Render der Daten am schirm

                // Resize the DataGridView columns to fit the newly loaded content.
                //DG_ResultsGrid.AutoResizeColumns(
                //DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

                DgTurnier.DataSource = datatbl;
                //DG_ResultsGrid.DataSource = bindingSource;
                //DG_ResultsGrid.DataSource = ds.DefaultViewManager;
            }
            else if (pt.Checked)
            {
                // Select Turnier
                Convert.ToByte(pt.Checked);
                string selectquery = string.Format("SELECT tName, tDatum FROM turnier ");

                adapter = new SqlDataAdapter(selectquery, con);
                SqlCommandBuilder sqlbuilder = new SqlCommandBuilder(adapter);

                datatbl.Clear();
                //DataSet ds = new DataSet();

                adapter.Fill(datatbl);
                //adapter.Fill(ds, "mitglied");

                bindingSource = new BindingSource();
                bindingSource.DataSource = datatbl;

                // Render der Daten am schirm

                // Resize the DataGridView columns to fit the newly loaded content.
                //DG_ResultsGrid.AutoResizeColumns(
                //DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

                DgTurnier.DataSource = datatbl;
                //DG_ResultsGrid.DataSource = bindingSource;
                //DG_ResultsGrid.DataSource = ds.DefaultViewManager;
            }
        }

        
        

    }
}
